insert overwrite table jms_dm.dm_taking_timely_platform_summary_hi
select
     sender_network_code      --寄件网点编码    
    ,sender_network_name      --寄件网点名称    
    ,sender_fran_code         --寄件加盟商编码  
    ,sender_fran_name         --寄件加盟商名称  
    ,sender_agent_code        --寄件代理区编码  
    ,sender_agent_name        --寄件代理区名称   
    ,sender_region_code       --寄件大区编码  
    ,sender_region_name       --寄件大区名称     
    ,count(case when order_source_name = '桃花岛' then order_no end) as thd_all_cnt --桃花岛总量
    ,count(case when order_source_name = '桃花岛' and if_tml_collect = 0 then order_no end) as thd_timely_cnt   --桃花岛揽收及时量
    ,count(case when order_source_name = '桃花岛' and if_tml_collect = 1 then order_no end) as thd_untimely_cnt --桃花岛未及时揽收量
    ,count(case when order_source_name = '紫金山' then order_no end) as zjs_all_cnt --紫金山总量
    ,count(case when order_source_name = '紫金山' and if_tml_collect = 0 then order_no end) as zjs_timely_cnt   --紫金山揽收及时量
    ,count(case when order_source_name = '紫金山' and if_tml_collect = 1 then order_no end) as zjs_untimely_cnt --紫金山未及时揽收量
    ,count(case when order_source_name = '逍遥峰' then order_no end) as xyf_all_cnt --逍遥峰总量
    ,count(case when order_source_name = '逍遥峰' and if_tml_collect = 0 then order_no end) as xyf_timely_cnt   --逍遥峰揽收及时量
    ,count(case when order_source_name = '逍遥峰' and if_tml_collect = 1 then order_no end) as xyf_untimely_cnt --逍遥峰未及时揽收量
    ,count(case when order_source_name = '七星潭' then order_no end) as qxt_all_cnt --七星潭总量
    ,count(case when order_source_name = '七星潭' and if_tml_collect = 0 then order_no end) as qxt_timely_cnt   --七星潭揽收及时量
    ,count(case when order_source_name = '七星潭' and if_tml_collect = 1 then order_no end) as qxt_untimely_cnt --七星潭未及时揽收量
    ,count(case when order_source_name = '极地湾' then order_no end) as jdw_all_cnt --极地湾总量
    ,count(case when order_source_name = '极地湾' and if_tml_collect = 0 then order_no end) as jdw_timely_cnt   --极地湾揽收及时量
    ,count(case when order_source_name = '极地湾' and if_tml_collect = 1 then order_no end) as jdw_untimely_cnt --极地湾未及时揽收量
    ,count(case when order_source_name not in ('桃花岛','紫金山','逍遥峰','七星潭','极地湾') then waybill_no end) as qt_all_cnt --其他总量
    ,count(case when order_source_name not in ('桃花岛','紫金山','逍遥峰','七星潭','极地湾') and if_tml_collect = 0 then order_no end) as qt_timely_cnt   --其他揽收及时量
    ,count(case when order_source_name not in ('桃花岛','紫金山','逍遥峰','七星潭','极地湾') and if_tml_collect = 1 then order_no end) as qt_untimely_cnt --其他未及时揽收量
    ,count(order_no) as all_cnt --总量
    ,count(case when if_tml_collect = 0 then order_no end) as timely_cnt   --揽收及时量
    ,count(case when if_tml_collect = 1 then order_no end) as untimely_cnt --未及时揽收量
    ,sum(case when order_source_name = '逍遥峰' and if_tml_collect = 0 then weight_value end) as xyf_timely_qz_cnt   --逍遥峰揽收及时量
    ,sum(case when order_source_name = '七星潭' and if_tml_collect = 0 then weight_value end) as qxt_timely_qz_cnt   --七星潭揽收及时量
    ,dt
from jms_dm.dm_taking_timely_detail_hi
where dt between date_sub('{{ execution_date | cst_ds }}',1) and '{{ execution_date | cst_ds }}'
group by sender_network_code      --寄件网点编码    
        ,sender_network_name      --寄件网点名称    
        ,sender_fran_code         --寄件加盟商编码  
        ,sender_fran_name         --寄件加盟商名称  
        ,sender_agent_code        --寄件代理区编码  
        ,sender_agent_name        --寄件代理区名称   
        ,sender_region_code       --寄件大区编码  
        ,sender_region_name       --寄件大区名称  
        ,dt 
distribute by pmod(hash(rand()),30);
